// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Data;

namespace SnapObjects.Data
{
    /// <summary>
    ///     SqlBuilder constructs SQL statements in a programmatic and database-agnostic way. With SqlBuilder you only need 
    ///     to build the syntax structure, and database-specific SQL statements will be generated based on the syntax structure and DataContext.
    ///     SqlBuilder removes the headache of mastering the SQL statements for various database type.Also, the constructed SQLs
    ///     can be cached, reused, copied and modified, which resolves the headache of maintaining large amount of SQL statements in a project.
    /// </summary>
    public class SqlBuilder
    {
        internal const string SQL_PLACEHOLD_FORMAT = "__P_P_H_S__prefix_{0}__name_{1}__P_P_H_E__";

        #region Builder
        /// <summary>
        ///     Creates an ISqlInsertBuilder object for building the SQL INSERT statement and specifies 
        ///     the name of the table or view that will receive the data.
        /// </summary>
        /// <param name="table">
        ///     The name of the table or view that will receive the data.
        /// </param>
        /// <returns>
        ///     Returns a newly created ISqlInsertBuilder object which can be used to build the SQL INSERT statement.
        /// </returns>
        public static ISqlInsertBuilder Insert(string table)
        {
            return Insert(table, "");
        }

        /// <summary>
        ///     Creates an ISqlDeleteBuilder object for building the SQL INSERT statement and specifies the name 
        ///     and the schema of the table or view that will receive the data.
        /// </summary>
        /// <param name="table">The name of the table or view that will receive the data.</param>
        /// <param name="schema">The schema of the table or view that will receive the data.</param>
        /// <returns>
        ///     Returns a newly created ISqlInsertBuilder object which can be used to build the SQL INSERT statement.
        /// </returns>
        public static ISqlInsertBuilder Insert(string table, string schema)
        {
            var builder = new SqlInsertBuilder(Guid.NewGuid().ToString());

            builder.Insert(table, schema);

            return builder;
        }

        /// <summary>
        ///     Creates an ISqlDeleteBuilder object for building the SQL DELETE statement, and specifies the name of 
        ///     the table or view from which the rows are to be removed.
        /// </summary>
        /// <param name="table">The name of the table or view from which the rows are to be removed.</param>
        /// <returns>
        ///     Returns a newly created ISqlDeleteBuilder object which can be used to build the SQL DELETE statement.
        /// </returns>
        public static ISqlDeleteBuilder Delete(string table)
        {
            return Delete(table, "");
        }

        /// <summary>
        ///     Creates an ISqlDeleteBuilder object for building the SQL DELETE statement and specifies the name and
        ///     the schema of the table or view from which the rows are to be removed.
        /// </summary>
        /// <param name="table">The name of the table or view from which the rows are to be removed.</param>
        /// <param name="schema">The name of the corresponding schema for the table.</param>
        /// <returns>
        ///     Returns a newly created ISqlDeleteBuilder object which can be used to build the SQL DELETE statement.
        /// </returns>
        public static ISqlDeleteBuilder Delete(string table, string schema)
        {
            var builder = new SqlDeleteBuilder(Guid.NewGuid().ToString());

            builder.Delete(table, schema);

            return builder;
        }

        /// <summary>
        ///     Creates an ISqlUpdateBuilder object for building a SQL UPDATE statement, and specifies the name
        ///     of the table or view from which the rows are to be updated.
        /// </summary>
        /// <param name="table">
        ///     The name of the table or view from which the rows are to be updated.
        /// </param>
        /// <returns>
        ///     Returns a newly created ISqlUpdateBuilder object which can be used to build the SQL UPDATE statement.
        /// </returns>
        public static ISqlUpdateBuilder Update(string table)
        {
            return Update(table, "");
        }

        /// <summary>
        ///     Creates an ISqlUpdateBuilder object for building a SQL UPDATE statement, and specifies the 
        ///     name and schema of the table or view from which the rows are to be updated.
        /// </summary>
        /// <param name="table">The name of the table or view from which the rows are to be updated.</param>
        /// <param name="schema">The name of the schema to which the table or view belongs.</param>
        /// <returns>
        ///     Returns a newly created ISqlUpdateBuilder object which can be used to build the SQL UPDATE statement.
        /// </returns>
        public static ISqlUpdateBuilder Update(string table, string schema)
        {
            var builder = new SqlUpdateBuilder(Guid.NewGuid().ToString());

            builder.Update(table, schema);

            return builder;
        }

        /// <summary>
        ///     Creates an ISqlQueryBuilder object for building a SQL SELECT statement, and adds a column to the select
        ///     list of the SELECT clause. You can also specify an alias for this column.
        /// </summary>
        /// <param name="column">
        ///     The name of the column to be added to the select list of the SELECT clause.
        /// </param>
        /// <param name="alias">
        /// (Optional) The alias of the column. The default value is an empty string.
        /// </param>
        /// <returns>
        ///     Returns a newly created ISqlQueryBuilder object which can be used to build the SQL SELECT statement.
        /// </returns>
        public static ISqlQueryBuilder Select(string column, string alias = "")
        {
            var builder = new SqlQueryBuilder(Guid.NewGuid().ToString());

            builder.Select(column, alias);

            return builder;
        }

        /// <summary>
        ///     Creates an ISqlQueryBuilder object for building a SQL SELECT statement, and adds multiple columns to the select list of the SELECT clause.
        /// </summary>
        /// <param name="columns">
        ///     An array of column names to be added to the select list of the SELECT clause.
        /// </param>
        /// <returns>
        ///     Returns a newly created ISqlQueryBuilder object which can be used to build the SQL SELECT statement.
        /// </returns>
        public static ISqlQueryBuilder Select(string[] columns)
        {
            var builder = new SqlQueryBuilder(Guid.NewGuid().ToString());

            builder.Select(columns);

            return builder;
        }

        /// <summary>
        ///     Creates an ISqlQueryBuilder object for building a SQL SELECT statement, 
        ///     and adds a raw SQL to the select list of the SELECT clause.
        /// </summary>
        /// <param name="selectRaw">
        ///     A string of raw SQL to be added to the SELECT clause. 
        ///     Actually, you can write any raw SQL if the SQL statement is valid.
        /// </param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns a newly created ISqlQueryBuilder object which can be used to build the SQL SELECT statement.
        /// </returns>
        public static ISqlQueryBuilder SelectRaw(string selectRaw, params ISqlParameter[] parameters)
        {
            var builder = new SqlQueryBuilder(Guid.NewGuid().ToString());

            builder.SelectRaw(selectRaw, parameters);

            return builder;
        }

        /// <summary>
        ///     Creates an ISqlQueryBuilder object for building a SQL SELECT statement and adds a column of SQL subquery (specified by 
        ///     an ISqlBuilder object) to the select list of the SELECT clause. You can specify an alias for this column.
        /// </summary>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery.</param>
        /// <param name="alias">
        ///     (Optional) The alias of the column. The default value is an empty string.
        /// </param>
        /// <returns>
        ///     Returns a newly created ISqlQueryBuilder object which can be used to build the SQL SELECT statement.
        /// </returns>
        public static ISqlQueryBuilder Select(ISqlBuilder sqlBuilder, string alias = "")
        {
            var builder = new SqlQueryBuilder(Guid.NewGuid().ToString());

            builder.Select(sqlBuilder, alias);

            return builder;
        }
        #endregion

        #region Parameter
        /// <summary>
        ///    Creates an ISqlParameter object which represents a SQL parameter and specifies the data type of the
        ///    parameter value by the Type parameter.
        /// </summary>
        /// <typeparam name="TType">The type of the parameter value.</typeparam>
        /// <param name="name">
        ///     The name of the SQL parameter. The default value of this parameter is String.Empty, which is unnamed. 
        ///     If you use the unnamed parameter in a ISqlBuilder object, they will be specified to @p0, @p1, ..., and 
        ///     @pn by the adding sequence of the parameters.
        /// </param>
        /// <returns>Returns the newly created SQL parameter.</returns>
        public static ISqlParameter Parameter<TType>(string name = "")
        {
            return Parameter(typeof(TType), name, "");
        }

        /// <summary>
        ///     Creates an ISqlParameter object which represents a SQL parameter and specifies the data type of the parameter value.
        /// </summary>
        /// <param name="type">The type of the parameter value.</param>
        /// <param name="name">
        ///     The name of the SQL parameter. The default value of this parameter is String.Empty, which is unnamed. 
        ///     If you use the unnamed parameter in a ISqlBuilder object, they will be specified to @p0, @p1, ..., 
        ///     and @pn by the adding sequence of the parameters.
        /// </param>
        /// <returns>Returns the newly created SQL parameter.</returns>
        public static ISqlParameter Parameter(Type type, string name = "")
        {
            return Parameter(type, name, "");
        }

        internal static ISqlParameter Parameter(Type type, string name = "", string prefix = "")
        {
            return Parameter(name, type, ParameterDirection.Input, prefix);
        }

        internal static ISqlParameter Parameter(
            string name, Type type, ParameterDirection direction, string prefix = "")
        {
            var placehold = String.Format(SQL_PLACEHOLD_FORMAT, prefix, name);

            return new SqlBuilderParameter(placehold, name, type, direction);
        }

        #endregion

        #region EmbeddedWhere
        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. Specifies a SQL expression on the left of the operator and a specific value on the 
        ///     right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereValue(string left, object value)
        {
            return EmbeddedWhereValue(left, SqlBinaryOperator.Equals, value);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. Specifies a SQL expression on the left of the operator and a specific value on the 
        ///     right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">
        ///     A specific value on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            var condition = new SqlWhereCondition(true);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the
        ///     right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhere(string left, ISqlParameter parameter)
        {
            return EmbeddedWhere(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the
        ///     right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhere(
            string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            var condition = new SqlWhereCondition(true);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhere(string left, string right)
        {
            return EmbeddedWhere(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhere(string left, SqlBinaryOperator sqlOperator, string right)
        {
            var condition = new SqlWhereCondition(true);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right. 
        ///     The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhere(string left, ISqlBuilder sqlBuilder)
        {
            return EmbeddedWhere(left, SqlBinaryOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhere(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            return EmbeddedWhere(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), sqlBuilder);
        }

        private static ISqlWhereCondition EmbeddedWhere(string left, SqlOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var condition = new SqlWhereCondition(true);

            var builder = (SqlBuilderBase)sqlBuilder;

            return condition.Create(left, sqlOperator, builder.Statement, SqlRightType.Statement);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. This search condition is specified by a raw SQL string.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereRaw(string clause, params ISqlParameter[] parameters)
        {
            return EmbeddedWhereRaw(clause, "", parameters);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. This search condition is specified by a raw SQL string. 
        ///     You need to specify a name for the raw SQL.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="whereName">The name for the raw SQL. It can be used to remove the raw SQL from the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereRaw(
            string clause, string whereName, params ISqlParameter[] parameters)
        {
            var condition = new SqlWhereCondition(true);

            return condition.Create(clause, parameters, whereName);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. Uses IS NULL operator to search for values that are null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereIsNull(string left)
        {
            return EmbeddedWhere(left, SqlBinaryOperator.Is, "null");
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the WHERE clause. Uses IS NOT NULL operator to search for values that are not null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NOT NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereIsNotNull(string left)
        {
            return EmbeddedWhere(left, SqlBinaryOperator.IsNot, "null");
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. Uses EXISTS to test for the existence of the record returned from a subquery.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the EXISTS operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereExists(ISqlBuilder sqlBuilder)
        {
            return EmbeddedWhere("", SqlOperator.Exists, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the WHERE clause. Uses the NOT EXISTS operator to test if the record returned from a subquery does not exist.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the NOT EXISTS operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition EmbeddedWhereNotExists(ISqlBuilder sqlBuilder)
        {
            return EmbeddedWhere("", SqlOperator.NotExists, sqlBuilder);
        }
        #endregion

        #region Where
        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     Specifies a SQL expression on the left of the operator and a specific value on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereValue(string left, object value)
        {
            return WhereValue(left, SqlBinaryOperator.Equals, value);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     Specifies a SQL expression on the left of the operator and a specific value on the right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression
        ///     on the left and the value on the right.
        /// </param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            var condition = new SqlWhereCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     Specifies a SQL expression on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter. 
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition Where(string left, ISqlParameter parameter)
        {
            return Where(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     Specifies a SQL expression on the left of the operator and a SQL parameter on the right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter. 
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition Where(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            var condition = new SqlWhereCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition Where(string left, string right)
        {
            return Where(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition Where(
            string left, SqlBinaryOperator sqlOperator, string right)
        {
            var condition = new SqlWhereCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     Specifies a SQL expression on the left of the operator and a SQL subquery on the right. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition Where(string left, ISqlBuilder sqlBuilder)
        {
            return Where(left, SqlOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition Where(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            return Where(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), sqlBuilder);
        }

        private static ISqlWhereCondition Where(string left, SqlOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var condition = new SqlWhereCondition(false);

            var builder = (SqlBuilderBase)sqlBuilder;

            return condition.Create(left, sqlOperator, builder.Statement, SqlRightType.Statement);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     Uses IS NULL operator to search for values that are null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereIsNull(string left)
        {
            return Where(left, SqlBinaryOperator.Is, "null");
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     Uses IS NOT NULL operator to search for values that are not null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NOT NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereIsNotNull(string left)
        {
            return Where(left, SqlBinaryOperator.IsNot, "null");
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     Uses EXISTS to test for the existence of the record returned from a subquery.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the EXISTSoperator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereExists(ISqlBuilder sqlBuilder)
        {
            return Where("", SqlOperator.Exists, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     Uses the NOT EXISTS operator to test if the record returned from a subquery does not exist.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the NOT EXISTS operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereNotExists(ISqlBuilder sqlBuilder)
        {
            return Where("", SqlOperator.NotExists, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause. 
        ///     This search condition is specified by a raw SQL string.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereRaw(string clause, params ISqlParameter[] parameters)
        {
            return WhereRaw(clause, "", parameters);
        }

        /// <summary>
        ///     Creates an ISqlWhereCondition object which represents a search condition that can be used when building the WHERE clause.
        ///     This search condition is specified by a raw SQL string. You need to specify a name for the raw SQL.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="whereName">
        ///     The name for the raw SQL. It can be used to remove the raw SQL from the WHERE clause.
        /// </param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereCondition object which can be used to add more search conditions to the WHERE clause.
        /// </returns>
        public static ISqlWhereCondition WhereRaw(string clause, string whereName, params ISqlParameter[] parameters)
        {
            var condition = new SqlWhereCondition(false);

            return condition.Create(clause, parameters, whereName);
        }

        #endregion

        #region EmbeddedHaving
        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a specific value on the 
        ///     right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHavingValue(string left, object value)
        {
            return EmbeddedHavingValue(left, SqlBinaryOperator.Equals, value);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a specific value on the 
        ///     right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHavingValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            var condition = new SqlHavingCondition(true);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the 
        ///     right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHaving(string left, ISqlParameter parameter)
        {
            return EmbeddedHaving(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the
        ///     right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHaving(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            var condition = new SqlHavingCondition(true);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHaving(string left, string right)
        {
            return EmbeddedHaving(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHaving(
            string left, SqlBinaryOperator sqlOperator, string right)
        {
            var condition = new SqlHavingCondition(true);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        ///     The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHaving(string left, ISqlBuilder sqlBuilder)
        {
            return EmbeddedHaving(left, SqlBinaryOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHaving(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var condition = new SqlHavingCondition(true);

            var builder = (SqlBuilderBase)sqlBuilder;

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), builder.Statement, SqlRightType.Statement);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. This search condition is specified by a raw SQL string.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the HAVING clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHavingRaw(string clause, params ISqlParameter[] parameters)
        {
            return EmbeddedHavingRaw(clause, "", parameters);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the HAVING clause. This search condition is specified by a raw SQL string. You need to specify a name 
        ///     for the raw SQL.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the HAVING clause.</param>
        /// <param name="havingName">The name for the raw SQL. It can be used to remove the raw SQL from the HAVING clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition EmbeddedHavingRaw(
            string clause, string havingName, params ISqlParameter[] parameters)
        {
            var condition = new SqlHavingCondition(true);

            return condition.Create(clause, parameters, havingName);
        }
        #endregion

        #region Having
        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause.
        ///     Specifies a SQL expression on the left of the operator and a specific value on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition HavingValue(string left, object value)
        {
            return HavingValue(left, SqlBinaryOperator.Equals, value);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause. 
        ///     Specifies a SQL expression on the left of the operator and a specific value on the right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">
        ///     A specific value on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition HavingValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            var condition = new SqlHavingCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building 
        ///     the HAVING clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the right
        ///     of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition Having(string left, ISqlParameter parameter)
        {
            return Having(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause. 
        ///     Specifies a SQL expression on the left of the operator and a SQL parameter on the right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition Having(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            var condition = new SqlHavingCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building
        ///     the HAVING clause. Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition Having(string left, string right)
        {
            return Having(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building 
        ///     the HAVING clause. Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">
        ///     A SQL expression on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition Having(
            string left, SqlBinaryOperator sqlOperator, string right)
        {
            var condition = new SqlHavingCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause. 
        ///     Specifies a SQL expression on the left of the operator and a SQL subquery on the right. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition Having(string left, ISqlBuilder sqlBuilder)
        {
            return Having(left, SqlBinaryOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause. 
        ///     Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition Having(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var condition = new SqlHavingCondition(false);

            var builder = (SqlBuilderBase)sqlBuilder;

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), builder.Statement, SqlRightType.Statement);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause. 
        ///     This search condition is specified by a raw SQL string.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the HAVING clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition HavingRaw(string clause, params ISqlParameter[] parameters)
        {
            return HavingRaw(clause, "", parameters);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used when building the HAVING clause. 
        ///     This search condition is specified by a raw SQL string. You need to specify a name for the raw SQL.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the HAVING clause.</param>
        /// <param name="havingName">
        ///     The name for the raw SQL. It can be used to remove the raw SQL from the HAVING clause.
        /// </param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingCondition object which can be used to add more search conditions to the HAVING clause.
        /// </returns>
        public static ISqlHavingCondition HavingRaw(
            string clause, string havingName, params ISqlParameter[] parameters)
        {
            var condition = new SqlHavingCondition(false);

            return condition.Create(clause, parameters, havingName);
        }
        #endregion

        #region EmbeddedJoinOn
        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the ON clause. Specifies a SQL expression on the left of the operator and a specific value on the right 
        ///     of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.</returns>
        public static ISqlJoinCondition EmbeddedOnValue(string left, object value)
        {
            return EmbeddedOnValue(left, SqlBinaryOperator.Equals, value);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the ON clause. Specifies a SQL expression on the left of the operator and a specific value on the 
        ///     right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOnValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the ON clause. Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOn(string left, string right)
        {
            return EmbeddedOn(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the ON clause. Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOn(string left, SqlBinaryOperator sqlOperator, string right)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the ON clause. Specifies a SQL expression on the left of the operator and a SQL parameter on the right 
        ///     of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOn(string left, ISqlParameter parameter)
        {
            return EmbeddedOn(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the ON clause. Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOn(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter);
        }

        /// <summary>
        ///     Creates an ISqlHavingCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the HAVING clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right. 
        ///     The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOn(string left, ISqlBuilder sqlBuilder)
        {
            return EmbeddedOn(left, SqlBinaryOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the ON clause. Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOn(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), sqlBuilder, SqlRightType.Statement);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses) 
        ///     when building the ON clause. This search condition is specified by a raw SQL string.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the ON clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOnRaw(string clause, params ISqlParameter[] parameters)
        {
            return EmbeddedOnRaw(clause, "", parameters);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used (enclosed in parentheses)
        ///     when building the ON clause. This search condition is specified by a raw SQL string. You need to specify a name for the raw SQL.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the ON clause.</param>
        /// <param name="conditionName">
        ///     The name for the raw SQL. It can be used to remove the raw SQL from the ON clause.
        /// </param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition EmbeddedOnRaw(
            string clause, string conditionName, params ISqlParameter[] parameters)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(clause, parameters, conditionName);
        }
        #endregion

        #region JoinOn
        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause.
        ///     Specifies a SQL expression on the left of the operator and a specific value on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition OnValue(string left, object value)
        {
            return OnValue(left, SqlBinaryOperator.Equals, value);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause. 
        ///     Specifies a SQL expression on the left of the operator and a specific value on the right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition OnValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause. 
        ///     Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.</returns>
        public static ISqlJoinCondition On(string left, string right)
        {
            return On(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause.
        ///     Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">
        ///     A SQL expression on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition On(string left, SqlBinaryOperator sqlOperator, string right)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause.
        ///     Specifies a SQL expression on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter. 
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition On(string left, ISqlParameter parameter)
        {
            return On(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause.
        ///     Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        /// 
        public static ISqlJoinCondition On(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause.
        ///     Specifies a SQL expression on the left of the operator and a SQL subquery on the right. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition On(string left, ISqlBuilder sqlBuilder)
        {
            return On(left, SqlBinaryOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause.
        ///     Specifies a SQL expression on the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition On(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var condition = new SqlJoinCondition(false);

            var builder = (SqlBuilderBase)sqlBuilder;

            return condition.Create(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), builder.Statement, SqlRightType.Statement);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause. 
        ///     This search condition is specified by a raw SQL string.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the ON clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition OnRaw(string clause, params ISqlParameter[] parameters)
        {
            return OnRaw(clause, "", parameters);
        }

        /// <summary>
        ///     Creates an ISqlJoinCondition object which represents a search condition that can be used when building the ON clause. 
        ///     This search condition is specified by a raw SQL string. You need to specify a name for the raw SQL.
        /// </summary>
        /// <param name="clause">A string of raw SQL to be added to the ON clause.</param>
        /// <param name="conditionName">
        ///     The name for the raw SQL. It can be used to remove the raw SQL from ON clause.
        /// </param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlJoinCondition object which can be used to add more search conditions to the ON clause.
        /// </returns>
        public static ISqlJoinCondition OnRaw(
            string clause, string conditionName, params ISqlParameter[] parameters)
        {
            var condition = new SqlJoinCondition(false);

            return condition.Create(clause, parameters, conditionName);
        }
        #endregion
    }
}
